<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt, stmt1, stmt2;

	if (request.getParameter("ACTION") != null) {
		if (request.getParameter("ACTION").equals("Insert")) {
			int NewID = 0;

			PreparedStatement statement = conn
					.prepareStatement("INSERT INTO probation(student_id, quater_from, year_from, quater_to, year_to, reason) VALUES (?,?,?,?,?,?)");
			statement.setInt(1, Integer.parseInt(request
					.getParameter("sltStudentName")));
			statement.setString(2,
					request.getParameter("sltStartQuarter"));
			statement.setString(3,
					request.getParameter("txtStartQuarter"));
			statement.setString(4,
					request.getParameter("sltEndQuarter"));
			statement.setString(5,
					request.getParameter("txtEndQuarter"));
			statement.setString(6, request.getParameter("txtReason"));

			statement.executeUpdate();
			statement.close();

		}

		if (request.getParameter("ACTION").equals("Update")) {
			int EditID = Integer.valueOf(request.getParameter("PARAMETER"));
			
			PreparedStatement statement = conn.prepareStatement(
					"UPDATE probation SET quater_from = ?, year_from = ?, quater_to = ?, year_to = ?, reason = ? WHERE probation_id = ?");
			
			statement.setString(1,
					request.getParameter("sltStartQuarter"+ String.valueOf(EditID)));
			statement.setString(2,
					request.getParameter("txtStartQuarter"+ String.valueOf(EditID)));

			statement.setString(3,
					request.getParameter("sltEndQuarter"+ String.valueOf(EditID)));
			statement.setString(4,
					request.getParameter("txtEndQuarter"+ String.valueOf(EditID)));
			statement.setString(5, request.getParameter("txtReason"+ String.valueOf(EditID)));
		
			statement.setInt(6, EditID);
			statement.executeUpdate();
			statement.close();

		}

		if (request.getParameter("ACTION").equals("Delete")) {
			int EditID = Integer.valueOf(request.getParameter("PARAMETER"));
					
			PreparedStatement statement = conn
					.prepareStatement("DELETE FROM probation WHERE probation_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();
		}
	}
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function Insert() {
		document.getElementById("sltStudentName").required = true;
		document.getElementById("sltStartQuarter").required = true;
		document.getElementById("txtStartQuarter").required = true;
		document.getElementById("sltEndQuarter").required = true;
		document.getElementById("txtEndQuarter").required = true;
		var start = parseInt(document.getElementById("txtStartQuarter").value);
		var end = parseInt(document.getElementById("txtEndQuarter").value);

		if (start > end) {
			document.getElementById("txtEndQuarter").setCustomValidity(
					"Start date must be less than End date");
		} else {
			if (start == end){
				var start1 = parseInt(document.getElementById("sltStartQuarter").value);
				var end1 = parseInt(document.getElementById("sltEndQuarter").value);
				if (start1 > end1) {
					document.getElementById("txtEndQuarter").setCustomValidity(
							"Start date must be less than End date");
				} else {
					document.getElementById("txtEndQuarter").setCustomValidity("");
				}
			}
			else {
				document.getElementById("txtEndQuarter").setCustomValidity("");
			}
		}

		document.getElementById("ACTION").value = "Insert";
	}
	function Update(row) {
		document.getElementById("sltStartQuarter"+row).required = true;
		document.getElementById("txtStartQuarter"+row).required = true;
		document.getElementById("sltEndQuarter"+row).required = true;
		document.getElementById("txtEndQuarter"+row).required = true;
		var start = parseInt(document.getElementById("txtStartQuarter"+row).value);
		var end = parseInt(document.getElementById("txtEndQuarter"+row).value);

		if (start > end) {
			document.getElementById("txtEndQuarter"+row).setCustomValidity(
					"Start date must be less than End date");
		} else {
			if (start == end){
				var start1 = parseInt(document.getElementById("sltStartQuarter"+row).value);
				var end1 = parseInt(document.getElementById("sltEndQuarter"+row).value);
				if (start1 > end1) {
					document.getElementById("txtEndQuarter"+row).setCustomValidity(
							"Start date must be less than End date");
				} else {
					document.getElementById("txtEndQuarter"+row).setCustomValidity("");
				}
			}
			else {
				document.getElementById("txtEndQuarter"+row).setCustomValidity("");
			}
		}

		document.getElementById("ACTION").value = "Update";
		document.getElementById("PARAMETER").value = row;

	}
	function Delete(row) {
		document.getElementById("ACTION").value = "Delete";
		document.getElementById("PARAMETER").value = row;
	}
</script>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<form id="myForm" method="post" action="probation.jsp">
					<p align="center">
						<font size="6">Probation</font>
					</p>
					<table border="1">
						<tr>
							<th>Student Name</th>
							<th>Start Quarter</th>
							<th>End Quarter</th>
							<th>Reason</th>
							<th>Action</th>
						</tr>
						<%---------- Add ----------%>
						<tr>
							<td><select name="sltStudentName" id="sltStudentName">
									<option value="">Select a student</option>
									<%
										stmt = conn.createStatement();
										ResultSet rsStudent = stmt.executeQuery("SELECT * FROM students");
										while (rsStudent.next()) {
									%>
									<option value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
						+ rsStudent.getString("middle_name") + " "
						+ rsStudent.getString("last_name")%></option>
									<%
										}
										rsStudent.close();
										stmt.close();
									%>
							</select></td>
							<td><select name="sltStartQuarter" id="sltStartQuarter">
									<option value="">Start Quarter</option>
									<option value="1">Fall</option>
									<option value="2">Winter</option>
									<option value="3">Spring</option>
									<option value="4">Summer</option>
							</select> <input type="text" name="txtStartQuarter" id="txtStartQuarter"
								size=2 pattern="\d+"></td>
							<td><select name="sltEndQuarter" id="sltEndQuarter">
									<option value="">End Quarter</option>
									<option value="1">Fall</option>
									<option value="2">Winter</option>
									<option value="3">Spring</option>
									<option value="4">Summer</option>
							</select> <input type="text" name="txtEndQuarter" id="txtEndQuarter"
								size=2 pattern="\d+"></td>
							<td><textarea name="txtReason" id="txtReason" rows="3"
									cols="20"></textarea></td>
							<td align="center"><input type="submit" value="Insert"
								onclick="Insert()"></td>
						</tr>
						<%---------- Edit ----------%>
						<%
							stmt = conn.createStatement();
							ResultSet rsProbation = stmt
									.executeQuery("SELECT * FROM probation");

							while (rsProbation.next()) {
								stmt1 = conn.createStatement();
								rsStudent = stmt1
										.executeQuery("SELECT * FROM students WHERE student_id="
												+ String.valueOf(rsProbation
														.getInt("student_id")));
						%>
						<tr>
							<td>
								<%
									if (rsStudent.next()) {
											out.print(rsStudent.getString("first_name") + " "
													+ rsStudent.getString("middle_name") + " "
													+ rsStudent.getString("last_name"));
										}
								%>
							</td>
							<td><select
								name="sltStartQuarter<%=rsProbation.getInt("probation_id")%>"
								id="sltStartQuarter<%=rsProbation.getInt("probation_id")%>">
									<option value="">Start Quarter</option>
									<%
										if (rsProbation.getString("quater_from").equals("1")) {
									%>
									<option value="1" selected>Fall</option>
									<%
										} else {
									%>
									<option value="1">Fall</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_from").equals("2")) {
									%>
									<option value="2" selected>Winter</option>
									<%
										} else {
									%>
									<option value="2">Winter</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_from").equals("3")) {
									%>
									<option value="3" selected>Spring</option>
									<%
										} else {
									%>
									<option value="3">Spring</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_from").equals("4")) {
									%>
									<option value="4" selected>Summer</option>
									<%
										} else {
									%>
									<option value="4">Summer</option>
									<%
										}
									%>

							</select> <input type="text"
								name="txtStartQuarter<%=rsProbation.getInt("probation_id")%>"
								id="txtStartQuarter<%=rsProbation.getInt("probation_id")%>"
								value="<%=rsProbation.getString("year_from")%>" size=2
								pattern="\d+"></td>
							<td><select
								name="sltEndQuarter<%=rsProbation.getInt("probation_id")%>"
								id="sltEndQuarter<%=rsProbation.getInt("probation_id")%>">
									<option value="">End Quarter</option>
									<%
										if (rsProbation.getString("quater_to").equals("1")) {
									%>
									<option value="1" selected>Fall</option>
									<%
										} else {
									%>
									<option value="1">Fall</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_to").equals("2")) {
									%>
									<option value="2" selected>Winter</option>
									<%
										} else {
									%>
									<option value="2">Winter</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_to").equals("3")) {
									%>
									<option value="3" selected>Spring</option>
									<%
										} else {
									%>
									<option value="3">Spring</option>
									<%
										}
									%>
									<%
										if (rsProbation.getString("quater_to").equals("4")) {
									%>
									<option value="4" selected>Summer</option>
									<%
										} else {
									%>
									<option value="4">Summer</option>
									<%
										}
									%>

							</select> <input type="text"
								name="txtEndQuarter<%=rsProbation.getInt("probation_id")%>"
								id="txtEndQuarter<%=rsProbation.getInt("probation_id")%>"
								value="<%=rsProbation.getString("year_to")%>" size=2
								pattern="\d+"></td>
							<td>
							<textarea name="txtReason<%=rsProbation.getInt("probation_id")%>" id="txtReason<%=rsProbation.getInt("probation_id")%>" rows="3"
									cols="20"><%=rsProbation.getString("reason")%></textarea></td>
							<td>
							<input type="submit" value="Update"
								onclick="Update(<%=rsProbation.getInt("probation_id")%>)">
								
								<input type="submit" value="Delete"
								onclick="Delete(<%=rsProbation.getInt("probation_id")%>)">
							
							</td>
						</tr>
						<%
							rsStudent.close();
								stmt1.close();
							}
						%>


					</table>
					<input type="hidden" id="ACTION" name="ACTION" value="None">
					<input type="hidden" id="PARAMETER" name="PARAMETER" value="">
				</form>
			</td>
		</tr>
	</table>
</body>
</html>
<%
	// Close the Connection
	conn.close();
%>